package day20131121;

import java.io.BufferedWriter;
import java.io.File;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Vector;

import com.oreilly.servlet.MultipartRequest;
/*import com.oreilly.servlet.MultipartRequest;*/
import com.sun.corba.se.spi.orbutil.fsm.Guard.Result;

import oracle.sql.CLOB;

public class Customer {

	private Connection conn;
	private PreparedStatement pstmt;

	CustomerDao cto = null;

	// 페이지 분리와 관계된 필드

	private int listSize = 5; // 페이지당 표시행수
	private int blockSize = 4; // 한 블록당 표시될 페이지 번호
	private int totSize = 0; // 검색된 전체 행수
	private int totPage = 0; // 전체 페이지 수
	private int totBlock = 0; // 전체 블록 수

	private int nowPage = 1;

	private int nowBlock = 1; // 현재 블록. 초기값 1
	private int startNo = 0; // 리스트 목록의 시작 위치
	private int endNo = 0; // 리스트 목록의 마지막 위치
	private int startPage = 1; // 하나의 블록에 표시할 시작 페이지 번호
	private int endPage = 4; // 하나의 블록에 표시할 끝 페이지 번호

	public Customer() {
		String url = "jdbc:oracle:thin:@127.0.0.1:1521";
		String user = "hr";
		String password = "hr";

		try {

			// 1) 오라클 드라이버 로딩
			Class.forName("oracle.jdbc.driver.OracleDriver");
			System.out.println("드라이버가 로딩됨...");

			// 2) 오라클 DB와 연결
			conn = DriverManager.getConnection(url, user, password);

			System.out.println("DB가 연결됨..");

		} catch (Exception ex) {
			ex.printStackTrace();
		}

	}

	public void pageCompute() {
		// this.totSize = totSize;

		totPage = (int) Math.ceil(totSize * 1.0 / listSize);
		totBlock = (int) Math.ceil(totPage * 1.0 / blockSize);

		nowBlock = (int) Math.ceil(nowPage * 1.0 / blockSize);

		endPage = nowBlock * blockSize;
		startPage = endPage - blockSize + 1;

		if (totPage < endPage)
			endPage = totPage;

		endNo = nowPage * listSize;
		startNo = endNo - listSize + 1;

		if (totSize < endNo)
			endNo = totSize;
	}

	public String readClob(CLOB content) {
		StringBuffer clobString = new StringBuffer();
		Reader reader = null;
		char[] buf = new char[1024];
		int readCnt = 0;

		try {
			reader = content.getCharacterStream();
			while ((readCnt = reader.read(buf, 0, 1024)) != -1) { // buf의 크기가
																	// 1024.
																	// read()는
																	// 읽은 데이터의
																	// 크기를
																	// return 함.
				clobString.append(buf, 0, readCnt); // 1024 미만의 내용은 append로 붙이는
													// 거.
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			return clobString.toString();
		}
	}

	public void saveClob(int n, String content) throws Exception {
		ResultSet rs = null;
		String sql = "SELECT content FROM help_desk WHERE serial = ? FOR UPDATE"; // for
																					// update를
																					// 써줘야
																					// 쓰기
																					// 가능한
																					// 상태로
																					// 읽어온다.
		conn.setAutoCommit(false); // 테이저의 일부만 저장된 상태에서 자동으로 commit 되는 걸 막아준다.
		pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, n);
		rs = pstmt.executeQuery();

		if (rs.next()) {
			CLOB clob = (CLOB) rs.getClob("content"); // Clob -> java.sql, CLOB
														// -> oracle.sql
			BufferedWriter bw = new BufferedWriter(clob.setCharacterStream(0));
			bw.write(content);
			bw.close();
		} else {
			System.out.println("fail");
		}

		rs.close();
		conn.commit(); // OracleDB 객체에서 conn 가져와야 함
		conn.setAutoCommit(true);
	}

	public Vector<CustomerDao> select(CustomerDao cto) {
		this.nowPage = cto.getNowPage();
		if (nowPage <= 0)
			nowPage = 1;
		String findStr = cto.getFindStr();

		Vector<CustomerDao> resultV = new Vector<CustomerDao>();
		String sql = null;
		ResultSet rs = null;

		try {

			// 전체 페이지수만 계산

			// 3) statement 생성
			sql = "SELECT COUNT(mid) no FROM help_desk "
					+ "WHERE (mid LIKE ? OR jemok LIKE ? OR content LIKE ? ) and part = ?";

			pstmt = conn.prepareStatement(sql); // ResultSet.TYPE_SCROLL_SENSITIVE,
												// ResultSet.CONCUR_READ_ONLY);

			pstmt.setString(1, "%" + findStr + "%");
			pstmt.setString(2, "%" + findStr + "%");
			pstmt.setString(3, "%" + findStr + "%");
			pstmt.setString(4, cto.getPart());

			// 4) statement 실행
			rs = pstmt.executeQuery();

			rs.next();
			totSize = rs.getInt("no");
			pageCompute();

			System.out.println("totSize=" + totSize);
			System.out.println("nowPage=" + nowPage);
			System.out.println("startNo=" + startNo);

			sql = "SELECT * FROM ( "
					+ "SELECT ROWNUM no, a.* FROM ( "
					+ "SELECT * FROM help_desk WHERE ( mid LIKE ? OR jemok LIKE ? OR content LIKE ? ) and part = ? ORDER BY mdate DESC"
					+ " )a " // 괄호안의 부분을 a 로 한다는 뜻...
					+ " ) WHERE no BETWEEN ? AND ?"; // no = ROWNUM number

			pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			pstmt.setString(1, "%" + findStr + "%");
			pstmt.setString(2, "%" + findStr + "%");
			pstmt.setString(3, "%" + findStr + "%");

			pstmt.setString(4, cto.getPart());

			pstmt.setInt(5, startNo);
			pstmt.setInt(6, endNo);

			rs = pstmt.executeQuery();

			// SELECT 문일때는 ... executeQuery()
			// INSERT, UPDATE, DELETE 문일때는 ... executeUpdate();

			// 5) 실행결과 출력

			while (rs.next()) {

				CustomerDao tempdbv = new CustomerDao();

				tempdbv.setSerial(rs.getInt("serial"));
				tempdbv.setPart(rs.getString("part"));
				tempdbv.setMid(rs.getString("mid"));
				tempdbv.setMdate(rs.getDate("mdate"));
				tempdbv.setJemok(rs.getString("jemok"));

				// select 라서 필요없음.
				//
				// tempdbv.setContentString(readClob(rs.getClob("content")));

				resultV.add(tempdbv);

			}

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {

			closeDB();
			return resultV;
		}

	}

	/*************************************************************
	 * 
	 * select
	 * 
	 * 
	 ************************************************************/

	public CustomerDao select() {

		ArrayList<String> files = new ArrayList<String>();

		// 3)statement 생성
		String sql = "SELECT * FROM help_desk";

		// String sql =
		// "SELECT serial, mid, mdate, passwd, jemok, DBMS_LOB.SUBSTR(content, DBMS_LOB.GETLENGTH(content), 1) content, hit, grp, deep, part FROM databank WHERE serial = ?";

		try {

			pstmt = conn.prepareStatement(sql);

			// 4) statement 실행
			ResultSet rs = pstmt.executeQuery();

			// SELECT 문일때는 ... executeQuery()
			// INSERT, UPDATE, DELETE 문일때는 ... executeUpdate();

			// 5) 실행결과 출력 모든 결과를 한번에 다 불러온다.

			while (rs.next()) {
				CustomerDao cto = new CustomerDao();

				cto.setSerial(rs.getInt("serial"));

				cto.setMid(rs.getString("mid"));
				cto.setMdate(rs.getDate("mdate"));
				cto.setJemok(rs.getString("jemok"));
				cto.setPart(rs.getString("part"));

			}
			rs.close();
		} catch (Exception ex) {

			ex.printStackTrace();

		} finally {

			closeDB();
			return cto;

		}

	}

	/*************************************************************
	 * 
	 * insert
	 * 
	 * 
	 ************************************************************/
	public int insert(CustomerDao cdo) {
		int r = 0;

		String sql = "";
		String sql1 = "";
		try {

			if (cdo.getPart().equals("QNA")) {
				sql = "insert into help_desk values(seq_help_desk.nextval,?,1,?,?,?,sysdate,0,0,?)";
			} else if (cdo.getPart().equals("FAQ")) {
				
				sql1 = "insert into help_desk values(seq_help_desk.nextval,administrator,0,administrator,?,?,sysdate,0,0,?)";
			}

			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, cdo.getMid());
			pstmt.setString(2, cdo.getNickname());
			pstmt.setString(3, cdo.getJemok());
			pstmt.setString(4, cdo.getContent());
			pstmt.setString(5, cdo.getPart());
			
			r = pstmt.executeUpdate();
			
			pstmt = conn.prepareStatement(sql1);
			
			pstmt.setString(1, cdo.getJemok());
			pstmt.setString(2, cdo.getContent());
			pstmt.setString(3, cdo.getPart());
			
			r = pstmt.executeUpdate();
			
			
			
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeDB();
		}
		return r;
	}

	/*************************************************************
	 * 
	 * rep_insert
	 * 
	 * deep 수정, grp, part 그대로
	 * 
	 ************************************************************/

	public int rep_insert(CustomerDao cto) {

		int r = 0;
		ResultSet rs = null;

		String sql = "SELECT seq_help_desk.nextval n from dual"; // 부를 때마다 증가
		// String sql = "SELECT seq_databank.currval n from dual";

		try {

			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			rs.next();
			int n = rs.getInt("n");

			sql = "INSERT INTO help_desk (serial, part, mid, mdate, passwd, jemok, "
					+ "content, hit, grp, deep) "
					+ "VALUES (?, ?, ?, sysdate, ?, ?, "
					+ "empty_clob(), 0, ?, ?)";

			// empty_clob() 대신에 ' '로 해도 됨.

			// serial, grp , deep, 은 n을 대입

			// 3)statement 생성
			pstmt = conn.prepareStatement(sql);

			// pstmt.setString(1, dbv.getPart());
			pstmt.setInt(1, n);
			pstmt.setString(2, cto.getPart());
			pstmt.setString(3, cto.getMid());
			pstmt.setString(4, cto.getJemok());

			// pstmt.setInt(5, dbv.getHit());
			// pstmt.setDate(10, sd.getMdate()); // Date 타입 처리하려면 귀찮음 -.-;;;;

			// 4) statement 실행
			r = pstmt.executeUpdate();
			rs.close();

			// SELECT 문일때는 ... executeQuery()
			// INSERT, UPDATE, DELETE 문일때는 ... executeUpdate();

			saveClob(n, cto.getContentString());

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {

			closeDB();
			return r;
		}

	}

	/*************************************************************
	 * 
	 * modify
	 * 
	 * 
	 ************************************************************/
public int modify(CustomerDao cdo){
	int r = 0;
	String sql = "";

	try{
		
			sql = "update help_desk set jemok=? , content=?  part=? where serial=?";
		
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, cdo.getJemok());
			pstmt.setString(2, cdo.getContent());
			pstmt.setString(3, cdo.getPart());
			pstmt.setInt(4, cdo.getSerial());
			r = pstmt.executeUpdate();
		

	
	
} catch (Exception ex) {
	ex.printStackTrace();
} finally {
	closeDB();
}
return r;
}


	
	
	
	

	/*************************************************************
	 * 
	 * delete
	 * 
	 * 
	 /************************************************************/

	public int delete(int serial) {
		
		
		int r =0;
		String	sql = "delete from help_desk  where serial=?";
		
		try{
					pstmt = conn.prepareStatement(sql);
					pstmt.setInt(1, serial);
					r = pstmt.executeUpdate();
				}catch(Exception ex){
					ex.printStackTrace();
				}finally{
					closeDB();
				}
				return r;
			}

		

	public void closeDB() {

		try {
			// if(rs != null) rs.close(); // 내가 넣은 거
			if (pstmt != null)
				pstmt.close();
			if (conn != null)
				conn.close();

		} catch (Exception ex) {
			ex.printStackTrace();
		}

	}

	public static void main(String[] args) {
		new Customer();
	}

	public int getListSize() {
		return listSize;
	}

	public void setListSize(int listSize) {
		this.listSize = listSize;
	}

	public int getBlockSize() {
		return blockSize;
	}

	public void setBlockSize(int blockSize) {
		this.blockSize = blockSize;
	}

	public int getTotSize() {
		return totSize;
	}

	public void setTotSize(int totSize) {
		this.totSize = totSize;
	}

	public int getTotPage() {
		return totPage;
	}

	public void setTotPage(int totPage) {
		this.totPage = totPage;
	}

	public int getTotBlock() {
		return totBlock;
	}

	public void setTotBlock(int totBlock) {
		this.totBlock = totBlock;
	}

	public int getNowPage() {
		return nowPage;
	}

	public void setNowPage(int nowPage) {
		this.nowPage = nowPage;
	}

	public int getNowBlock() {
		return nowBlock;
	}

	public void setNowBlock(int nowBlock) {
		this.nowBlock = nowBlock;
	}

	public int getStartNo() {
		return startNo;
	}

	public void setStartNo(int startNo) {
		this.startNo = startNo;
	}

	public int getEndNo() {
		return endNo;
	}

	public void setEndNo(int endNo) {
		this.endNo = endNo;
	}

	public int getStartPage() {
		return startPage;
	}

	public void setStartPage(int startPage) {
		this.startPage = startPage;
	}

	public int getEndPage() {
		return endPage;
	}

	public void setEndPage(int endPage) {
		this.endPage = endPage;
	}

	public CustomerDao getCto() {
		return cto;
	}

	public void setCto(CustomerDao cto) {
		this.cto = cto;
	}

}
